package br.edu.univas.si.engs.model;

import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.text.DateFormat;
import java.text.ParseException;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.Date;

import javax.swing.JOptionPane;

public class CadastroDAO extends ConnectDB {

	public AddressTO editAdress(int id) throws SQLException {

		String select;

		select = "select * from endereco where id = " + id;

		PreparedStatement stmt = getConnection().prepareStatement(select);
		ResultSet rs = stmt.executeQuery();

		AddressTO adress = new AddressTO();
		while (rs.next()) {

			adress.setRua(rs.getString("rua"));
			adress.setBairro(rs.getString("bairro"));
			adress.setCep(rs.getString("cep"));
			adress.setCidade(rs.getString("cidade"));
			adress.setEstado(rs.getString("estado"));
			adress.setNumero(rs.getInt("numero"));

			System.out.println("encontrar:"+adress.getRua());
		}

		rs.close();
		stmt.close();
		return adress;
	}

	public ClientTO editClientes(int id) throws SQLException {

		String select;

		select = "select * from clientes where id = " + id;

		PreparedStatement stmt = getConnection().prepareStatement(select);
		ResultSet rs = stmt.executeQuery();

		ClientTO cliente = new ClientTO();
		while (rs.next()) {

			cliente.setId(rs.getInt("id"));
			cliente.setNome(rs.getString("nome"));
			cliente.setCpf(rs.getString("cpf"));
			cliente.setRg(rs.getString("rg"));
			cliente.setTelefone(rs.getString("telefone"));
			cliente.setEmail(rs.getString("email"));
			cliente.setCelular(rs.getString("celular"));
			cliente.setDataNasc(rs.getString("dataNasc"));
			cliente.setDataCad(rs.getString("dataCad"));
			cliente.setStatus(rs.getBoolean("status"));
			cliente.setEndereco(rs.getInt("endereco"));
			System.out.println(cliente.getNome());
		}

		rs.close();
		stmt.close();

		return cliente;
	}

	public ArrayList<ClientTO> findClientes(String nome) throws SQLException {
		ArrayList<ClientTO> clientes = new ArrayList<ClientTO>();

		String select;
		if (nome.equals(""))
			select = "SELECT * FROM CLIENTES ORDER BY NOME ASC";
		else
			select = "select * from clientes where nome like '%" + nome + "%'";

		PreparedStatement stmt = getConnection().prepareStatement(select);
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			ClientTO cliente = new ClientTO();
			cliente.setId(rs.getInt("id"));
			cliente.setNome(rs.getString("nome"));
			cliente.setCpf(rs.getString("cpf"));
			cliente.setRg(rs.getString("rg"));
			cliente.setTelefone(rs.getString("telefone"));
			cliente.setEmail(rs.getString("email"));
			cliente.setCelular(rs.getString("celular"));
			cliente.setDataNasc(rs.getString("dataNasc"));
			cliente.setDataCad(rs.getString("dataCad"));
			cliente.setStatus(rs.getBoolean("status"));
			clientes.add(cliente);

		}
		rs.close();
		stmt.close();

		return clientes;
	}

	public boolean saveClient(ClientTO clientTO, AddressTO addressTO) {

		String sqlAddClient = "insert into clientes (cpf, rg, nome, email, telefone, celular, datacad, datanasc, status, endereco)"
				+ "values (?,?,?,?,?,?,?,?,?,?)";

		String sqlAddAddress = "insert into endereco(cidade, cep, estado, rua, numero, bairro)"
				+ "values (?,?,?,?,?,?)";

		try {
			PreparedStatement client = getConnection().prepareStatement(
					sqlAddClient);

			PreparedStatement address = getConnection().prepareStatement(
					sqlAddAddress);

			client.setString(1, clientTO.getCpf());
			client.setString(2, clientTO.getRg());
			client.setString(3, clientTO.getNome());
			client.setString(4, clientTO.getEmail());
			client.setString(5, clientTO.getTelefone());
			client.setString(6, clientTO.getCelular());
			client.setString(7, clientTO.getDataCad());
			client.setString(8, clientTO.getDataNasc());
			client.setBoolean(9, clientTO.getStatus());
			client.setInt(10, getMaxId());
			address.setString(1, addressTO.getCidade());
			address.setString(2, addressTO.getCep());
			address.setString(3, addressTO.getEstado());
			address.setString(4, addressTO.getRua());
			address.setInt(5, addressTO.getNumero());
			address.setString(6, addressTO.getBairro());
			address.executeUpdate();
			client.executeUpdate();
			address.close();
			client.close();
			return true;

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	/*
	 * public java.sql.Date validarDate(String data) throws ParseException {
	 * SimpleDateFormat df = new SimpleDateFormat("dd/MM/yyyy");
	 * 
	 * Date dataUtil = df.parse(data); java.sql.Date dataSql = null;
	 * 
	 * try { dataUtil = new java.sql.Date(dataUtil.getTime()); dataSql =
	 * (java.sql.Date) dataUtil;
	 * 
	 * } catch (Exception e) { JOptionPane.showMessageDialog(null,
	 * "Erro ao converte data para sql: " + e.getMessage()); }
	 * 
	 * return dataSql; }
	 */
	public int getMaxId() {
		String select = "SELECT MAX(id) FROM ENDERECO";

		try {

			PreparedStatement stmt = getConnection().prepareStatement(select);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			int maxID = rs.getInt(1);
			if (maxID == 0) {
				maxID = 0;
			}
			rs.close();
			stmt.close();
			System.out.println("id mazx:"+maxID);
			return maxID + 1;

		} catch (Exception e) {
			// TODO: handle exception
		}
		return 0;
	}

	public boolean updateClient(ClientTO clientTO, AddressTO addressTO) {
		System.out.println("updateClient( " + clientTO.getNome() + "*****"
				+ addressTO.getRua() + "*****" + clientTO.getEndereco());

		String sqlAddClient = "update clientes set cpf=?, rg=?, nome=?, email=?, telefone=?, celular=?, datacad=?, datanasc=?, status=?"
				+ "where endereco=?";

		String sqlAddAddress = "update endereco set cidade=?, cep=?, estado=?, rua=?, numero=?, bairro=?"
				+ "where id= ?";

		try {
			PreparedStatement client = getConnection().prepareStatement(
					sqlAddClient);

			PreparedStatement address = getConnection().prepareStatement(
					sqlAddAddress);

			client.setString(1, clientTO.getCpf());
			client.setString(2, clientTO.getRg());
			client.setString(3, clientTO.getNome());
			client.setString(4, clientTO.getEmail());
			client.setString(5, clientTO.getTelefone());
			client.setString(6, clientTO.getCelular());
			client.setString(7, clientTO.getDataCad());
			client.setString(8, clientTO.getDataNasc());
			client.setBoolean(9, clientTO.getStatus());
			client.setInt(10, clientTO.getEndereco());
			address.setString(1, addressTO.getCidade());
			address.setString(2, addressTO.getCep());
			address.setString(3, addressTO.getEstado());
			address.setString(4, addressTO.getRua());
			address.setInt(5, addressTO.getNumero());
			address.setString(6, addressTO.getBairro());
			address.setInt(7, clientTO.getEndereco());
			address.executeUpdate();
			client.executeUpdate();
			address.close();
			client.close();
			return true;

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public void getExcluir(int id) {
		String select = "SELECT ENDERECO FROM CLIENTES WHERE ID=" + id;

		String sqlAddClient = "DELETE  FROM CLIENTES WHERE ENDERECO= ?";

		String sqlAddAddress = "DELETE  FROM ENDERECO WHERE ID= ?";
		int idEndereco = 0;
		try {
			PreparedStatement stmt = getConnection().prepareStatement(select);
			ResultSet rs = stmt.executeQuery();
			rs.next();
			idEndereco = rs.getInt(1);
			rs.close();
			stmt.close();
			System.out.println("getEXCLUIR " + idEndereco);

			PreparedStatement client = getConnection().prepareStatement(
					sqlAddClient);

			PreparedStatement address = getConnection().prepareStatement(
					sqlAddAddress);
			client.setInt(1, idEndereco);
			address.setInt(1, idEndereco);

			address.executeUpdate();
			client.executeUpdate();
			address.close();
			client.close();

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
		}

	}

	// -----------------------------------CAR-DAO----------------------------------------------

	public ArrayList<CarTO> findCar(String nome) throws SQLException {
		ArrayList<CarTO> car = new ArrayList<CarTO>();

		String select;
		if (nome.equals(""))
			select = "SELECT * FROM carro ORDER BY modelo ASC";
		else
			select = "select * from carro where modelo like '%" + nome + "%'";

		PreparedStatement stmt = getConnection().prepareStatement(select);
		ResultSet rs = stmt.executeQuery();

		while (rs.next()) {
			
			CarTO car1 = new CarTO();
			System.out.println("interno valor:"+ car1.getPrecoHora());
			car1.setId(rs.getInt("id"));
			car1.setMarca(rs.getString("marca"));
			car1.setModelo(rs.getString("modelo"));
			car1.setAno(rs.getInt("ano"));
			car1.setPlaca(rs.getString("placa"));
			car1.setCor(rs.getString("cor"));
			car1.setPrecoHora(rs.getFloat("precohoras"));
			car1.setMulta(rs.getFloat("multas"));
			car1.setJuros(rs.getInt("juros"));
			car.add(car1);

		}
		rs.close();
		stmt.close();

		return car;
	}

	public boolean saveCar(CarTO car1) {
		System.out.println("valor:"+car1.getPrecoHora());
		String sqlAddClient = "insert into carro (marca, modelo, ano, placa, cor, precohoras, multas, juros)"
				+ "values (?,?,?,?,?,?,?,?)";

		try {
			PreparedStatement car = getConnection().prepareStatement(
					sqlAddClient);

			car.setString(1, car1.getMarca());
			car.setString(2, car1.getModelo());
			car.setInt(3, car1.getAno());
			car.setString(4, car1.getPlaca());
			car.setString(5, car1.getCor());
			car.setDouble(6, car1.getPrecoHora());
			car.setDouble(7, car1.getMulta());
			car.setFloat(8, car1.getJuros());

			car.executeUpdate();
			car.close();
			return true;

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
			return false;
		}
	}

	public boolean updatCar(CarTO car, int id) {
		System.out.println("id:"+id+"*****");
		// addressTO.getRua()+"*****"+ clientTO.getEndereco());

		String sqlAddClient = "update carro set marca=?, modelo=?, ano=?, placa=?, cor=?, precohoras=?, multas=?, juros=?"
				+ "where id="+id;

		try {
			PreparedStatement cart = getConnection().prepareStatement(
					sqlAddClient);

			cart.setString(1, car.getMarca());
			cart.setString(2, car.getModelo());
			cart.setInt(3, car.getAno());
			cart.setString(4, car.getPlaca());
			cart.setString(5, car.getCor());
			cart.setFloat(6, car.getPrecoHora());
			cart.setFloat(7, car.getMulta());
			cart.setFloat(8, car.getJuros());
			

			cart.executeUpdate();
			cart.close();
			return true;

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
			return false;
		}
	}
	
	public CarTO editCar(int id) throws SQLException {

		String select;

		select = "select * from carro where id = " + id;

		PreparedStatement stmt = getConnection().prepareStatement(select);
		ResultSet rs = stmt.executeQuery();

		CarTO car = new CarTO();
		while (rs.next()) {

			car.setId(rs.getInt("id"));
			car.setMarca(rs.getString("marca"));
			car.setModelo(rs.getString("modelo"));
			car.setAno(rs.getInt("ano"));
			car.setPlaca(rs.getString("placa"));
			car.setCor(rs.getString("cor"));
			car.setPrecoHora(rs.getInt("precohoras"));
			car.setMulta(rs.getFloat("multas"));
			car.setJuros(rs.getInt("juros"));
			
		
		}

		rs.close();
		stmt.close();

		return car;
	}
	
	public void getExcluirVeiculo(int id) {
		

		String sqlAddClient = "DELETE  FROM CARRO WHERE ID= ?";

		
		try {

			System.out.println("getEXCLUIR " + id);

			PreparedStatement car = getConnection().prepareStatement(
					sqlAddClient);

			
			car.setInt(1, id);
		

			
			car.executeUpdate();
			
			car.close();

		} catch (Exception e) {
			System.out.println("Erro: " + e.getMessage());
			e.printStackTrace();
		}

	}

}
